package com.travel.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class DBUtils {
	
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static String driverName;
	private static String url;
	private static String username;
	private static String password;

	private static void loadDBProperties() { // 配置数据库连接
		ResourceBundle resourceBundle = ResourceBundle.getBundle("DB");
		driverName = resourceBundle.getString("db.driverName");
		url = resourceBundle.getString("db.url");
		username = resourceBundle.getString("db.username");
		password = resourceBundle.getString("db.password");

	}

	// 自动连接数据库
	static {
		loadDBProperties();
	}

	// 返回一个数据库连接
	public Connection getConnection() {
		try {
			Class.forName(driverName);
			connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return connection;
	}

	// select 方法
	/*
	 * public ResultSet selectGame(String sql, Object... params) { getConnection();
	 * try { preparedStatement = connection.prepareStatement(sql); if (params !=
	 * null) { for (int i = 0; i < params.length; i++) {
	 * preparedStatement.setObject(i + 1, params[i]); }
	 * 
	 * } resultSet = preparedStatement.executeQuery();
	 * 
	 * } catch (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); } return resultSet; }
	 */

	// 执行select
	public synchronized CachedRowSet query(String sql, Object... params) {
		getConnection();
		CachedRowSet cachedRowSet = null;
		try {
			RowSetFactory rowSetFactory = RowSetProvider.newFactory();
			cachedRowSet = rowSetFactory.createCachedRowSet();
			preparedStatement = connection.prepareStatement(sql);

			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}

			}
			resultSet = preparedStatement.executeQuery();
			cachedRowSet.populate(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			close();

		}
		return cachedRowSet;
	}

	// 执行insert 、 update 、delete
	public int update(String sql, Object... params) {
		int result = 0;
		getConnection();// 连接数据库
		try {
			preparedStatement = connection.prepareStatement(sql);

			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);

				}

			}
			result = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return result;
	}

	// 关闭
	public void close() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
